package com.bdqn.wang.dao.impl;

import com.bdqn.wang.dao.Menberinfodao;
import com.bdqn.wang.pojo.Menber;
import com.sun.org.apache.xpath.internal.compiler.Keywords;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class MenberinfodaoImpl extends BaseDao implements Menberinfodao {
    @Override
    public List<Menber> getAll() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = getConnection();
            String sql = "SELECT id,easybuy_user.loginName,easybuy_user.userName,sex,easybuy_user.type FROM `easybuy_user`";
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            List<Menber> list = new ArrayList<Menber>();
            while (resultSet.next()) {

                Menber user = new Menber();
                user.setId(resultSet.getInt(1));
                user.setLoginName(resultSet.getString(2));
                user.setUserName(resultSet.getString(3));
                user.setSex(resultSet.getInt(4));
                user.setType(resultSet.getString(5));

                list.add(user);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll(resultSet, preparedStatement, connection);
        }

        return null;
    }

    @Override
    public int add(Menber user) {
        String sql = " INSERT  INTO `easybuy_user`(id,loginName,userName,easybuy_user.`password`,sex,identityCode,mobile,email) VALUES (DEFAULT,?,?,?,?,?,?,?)";
        Object[] objects = {user.getLoginName(), user.getUserName(), user.getPassword(),user.getSex(),user.getIdentityCode(),user.getMobile(),user.getEmail()};
        return executeUpdate(sql, objects);
    }

    @Override
    public int delet(int id) {
        String sql = " DELETE FROM easybuy_user WHERE id= ?";
        Object[] objects = {id};
        return executeUpdate(sql, objects);
    }

    @Override
    public int update(Menber user) {
        String sql = "UPDATE easybuy_user SET loginName=?,userName=?,sex=?, type=?  WHERE id=?";
        Object[] objects = {user.getLoginName(), user.getUserName(), user.getSex(), user.getType(), user.getId()};
        return executeUpdate(sql, objects);
    }

    @Override
    public Menber getOne(int id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = getConnection();
            String sql = "select id,easybuy_user.loginName,easybuy_user.userName,sex,easybuy_user.type  from easybuy_user where id=?  ";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setObject(1, id);
            resultSet = preparedStatement.executeQuery();
            Menber user = new Menber();
            if (resultSet.next()) {
                user.setId(resultSet.getInt(1));
                user.setLoginName(resultSet.getString(2));
                user.setUserName(resultSet.getString(3));
                user.setSex(resultSet.getInt(4));
                user.setType(resultSet.getString(5));
            }
            return user;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll(resultSet, preparedStatement, connection);
        }
        return null;
    }

    @Override
    public List<Menber> getAllByPage(int currentPage) {
        Connection connection = getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        String sql = "SELECT id,easybuy_user.loginName,easybuy_user.userName,sex,type FROM `easybuy_user` limit ?,?";

        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setObject(1, (currentPage - 1) * 5);
            preparedStatement.setObject(2, 5);

            resultSet = preparedStatement.executeQuery();
            //从结果集当中取出来存给数据
            List<Menber> list = new ArrayList<Menber>();

            //定义一个几何
            //遍历结果集

            while (resultSet.next()) {
                Menber user = new Menber();
                //每次循环都创建一个新的keywords对象
                user.setId(resultSet.getInt(1));
                user.setLoginName(resultSet.getString(2));
                user.setUserName(resultSet.getString(3));
                user.setSex(resultSet.getInt(4));
                user.setType(resultSet.getString(5));
                //添加
                list.add(user);
            }
            //返回值
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll(resultSet, preparedStatement, connection);
        }
        return null;
    }

    @Override
    public int count() {
        Connection connection = getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        String sql = "SELECT COUNT(*) FROM `easybuy_user`";
        try {
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                return resultSet.getInt(1);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll(resultSet, preparedStatement, connection);
        }
        return  0;
    }
}
